package work.chief.dao.impl;

import work.chief.dao.UserDao;
import work.chief.entity.User;
import work.chief.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public User selectByUsernameAndPassword(String username, String password) {
        User user=null;

        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql = "select * from user where username=? and password=?";
            //构建一个数组给占位符进行赋值
            Object[] objects = {username, password};
            user = qr.query(sql, new BeanHandler<>(User.class), objects);
        }catch(SQLException e){
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public User selectByPhoneNum(String phone_number) {
        User user = null;
        //ctrl+Alt+t，填充try/catch
        try {
            QueryRunner queryRunner= DBUtils.getInstance().getQrDb();
            String sql="select * from user where phone_number=?";
            user=queryRunner.query(sql,new BeanHandler<User>(User.class),phone_number);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public User selectByUserName(String username) {
        User user=null;
        try {
            QueryRunner queryRunner=DBUtils.getInstance().getQrDb();
            String sql = "select * from user where username=?";
            user=queryRunner.query(sql,new BeanHandler<>(User.class),username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public int register(User user) {
        int num=0;
        try {
            String sql ="insert into user(name,sex,phone_number,area,username,password,photo,create_time)values(?,?,?,?,?,?,?,?)";
            Object [] objects={user.getName(),user.getSex(),user.getPhone_number(),user.getArea(),user.getUsername(),user.getPassword(),user.getPhoto(),user.getCreate_time()};
            QueryRunner queryRunner=DBUtils.getInstance().getQrDb();
            num=queryRunner.update(sql,objects);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return num;
    }

    @Override
    public User selectByUsernameAndPasswordAndManager(String username, String password, int manager) {

        User user = null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select * from user where username=? and password=? and manager=?";
            Object[] objects={username,password,manager};
            user = qr.query(sql, new BeanHandler<>(User.class), objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public List<User> selectAll() {

        List<User> userList=null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select * from user";
            userList = qr.query(sql, new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public List<User> selectPage(Integer currentPage,Integer pageSize) {

        List<User> userList = null;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select * from user limit ?,?";
            Object [] objects={(currentPage-1)*pageSize,pageSize};
            userList = qr.query(sql, new BeanListHandler<User>(User.class), objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userList;
    }

    @Override
    public Integer selectPageCount() {

        long count = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="select count(1) from user";
            count = (Long)qr.query(sql, new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Integer.parseInt(count+"");
    }

    @Override
    public int updateManager(int id, int manager) {
        int num = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="update user set manager=? where id=?";
            Object [] objects={manager,id};
            num = qr.update(sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return num;
    }

    @Override
    public int deleteUsers(String ids) {

        int num = 0;
        try {
            QueryRunner qr = DBUtils.getInstance().getQrDb();
            String sql="delete from user where id in("+ids+")";
            num = qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return num;
    }


}
